﻿--use PolePosition
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_VendorVisitXMissionStructure_SelectForGrid')
	DROP PROCEDURE up_VendorVisitXMissionStructure_SelectForGrid
GO

CREATE PROCEDURE up_VendorVisitXMissionStructure_SelectForGrid
	 @Vendor_ProjectFK int
	,@PageSize int = 100
	,@PageNumber int = 1
	,@SearchByType varchar(50) = null
	,@SearchByText varchar(50) = null
	
AS

SET NOCOUNT ON	

DECLARE @StartRowNumber int
DECLARE @EndRowNumber int

SET @StartRowNumber = ((@PageNumber - 1) * @PageSize) + 1
SET @EndRowNumber = @PageNumber * @PageSize


DECLARE @table table
(
 	 RowNumber int
	,TotalCount int
	,Vendor_Visit_XMissionStructureId int
	,Vendor_ProjectFK int
	,Utility_Asset_XMissionStructureFK int
	,Vendor_Visit_TypeFK int
	,XMissionStructures_TopTypeFk int
	,Latitude float
	,Longitude float
	,Altitude float
	,SatelliteCount int
	,SatelliteTime datetime
	,SolutionAvailable bit
	,HandheldFk int
	,Vendor_ContactFK int
	,[Date] datetime
	,HaveImages int
	,[FriendlyName] varchar(255)
)
	
INSERT @table
(
	 [RowNumber]
	,[TotalCount]
	,[Vendor_Visit_XMissionStructureId]
	,[Vendor_ProjectFK]
	,[Utility_Asset_XMissionStructureFK]
	,[Vendor_Visit_TypeFK]
	,[XMissionStructures_TopTypeFk]
	,[Latitude]
	,[Longitude]
	,[Altitude]
	,[SatelliteCount]
	,[SatelliteTime]
	,[SolutionAvailable]
	,[HandheldFk]
	,[Vendor_ContactFK]
	,[Date]
	,[HaveImages]
	,[FriendlyName]
)
SELECT 
*
FROM 
(
	SELECT 
		 ROW_NUMBER() OVER (ORDER BY Vendor_Visit_XMissionStructureId) AS RowNumber
		,COUNT(Vendor_Visit_XMissionStructureId) OVER (PARTITION BY NULL) AS TotalCount
		,[Vendor_Visit_XMissionStructureId]
		,[Vendor_ProjectFK]
		,[Utility_Asset_XMissionStructureFK]
		,[Vendor_Visit_TypeFK]
		,[XMissionStructures_TopTypeFk]
		,[Latitude]
		,[Longitude]
		,[Altitude]
		,[SatelliteCount]
		,[SatelliteTime]
		,[SolutionAvailable]
		,[HandheldFk]
		,[Vendor_ContactFK]
		,[Date]
		,[HaveImages]
		,[FriendlyName]
	FROM v_tb_Vendor_Visit_XMissionStructures
	WHERE
		Vendor_ProjectFK = @Vendor_ProjectFK
) AS Data
WHERE RowNumber BETWEEN @StartRowNumber AND @EndRowNumber


-- primary select
SELECT * FROM @table


	-- VendorVisitXMissionStructuresPoleCollection select

SELECT 
	v_tb_Vendor_Visit_XMissionStructures_Poles.* 
FROM 
	v_tb_Vendor_Visit_XMissionStructures_Poles 
	INNER JOIN @table x 
		ON v_tb_Vendor_Visit_XMissionStructures_Poles.Vendor_Visit_XMissionStructureFK = x.Vendor_Visit_XMissionStructureId



	-- VendorVisitXMissionStructuresImageCollection select

SELECT
	v_tb_Vendor_Visit_XMissionStructures_Images.* 
FROM 
	v_tb_Vendor_Visit_XMissionStructures_Images 
	INNER JOIN @table x 
		ON v_tb_Vendor_Visit_XMissionStructures_Images.Vendor_Visit_XMissionStructureFK = x.Vendor_Visit_XMissionStructureId

GO
